package com.chiao.dao.admin;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.chiao.core.BaseDao;
import com.chiao.entity.admin.Dept;

@Repository
public class DeptDao extends BaseDao{

	private static final String LIST_DEPT_SQL="SELECT * FROM DEPT";
	private static final String INSERT_DEPT_SQL="INSERT INTO DEPT(PID,SORTNO,DEPTNAME) VALUES(?,?,?)";
	private static final String UPDATE_DEPT_SQL="UPDATE dept SET  pid=?, sortno=?, deptname=? WHERE deptid=?";
	private static final String LIST_DEPTBYID_SQL="WITH RECURSIVE r AS (select d1.*,d2.deptname pname from dept d1,dept d2 where d1.pid = d2.deptid and d1.deptid=? union ALL SELECT DEPT.*,r.deptname pname FROM DEPT, r WHERE DEPT.pid = r.deptid) SELECT * FROM r order by pid,sortno";
	
	
	public List<Map<String,Object>> listDepts(){
		return queryForList(LIST_DEPT_SQL);
	}
	
	public int addDept(Dept d){
		return update(INSERT_DEPT_SQL, d.getPid(),d.getSortno(),d.getDeptname());
	}
	
	public List<Dept> listDeptsById(int id){
		return queryForList(Dept.class, LIST_DEPTBYID_SQL,id);
	}

	public int deleteDeptByIds(String ids) {
		String sql="delete from dept where deptid in( WITH RECURSIVE r AS (  SELECT * FROM dept WHERE deptid in("+ids+")  union   ALL   SELECT dept.* FROM dept, r WHERE dept.pid = r.deptid  )  SELECT deptid FROM r )";
		return  update(sql);
	}

	public int updateDept(Dept dept) {
		return update(UPDATE_DEPT_SQL,dept.getPid(),dept.getSortno(),dept.getDeptname(),dept.getDeptid());
	}
	
}
